package org.wiztools.thegreatwall;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import org.wiztools.commons.DigestUtil;

/**
 *
 * @author subwiz
 */
public class MySQLOperations implements Operations {

    private static final Logger LOG = Logger.getLogger(MySQLOperations.class.getName());

    public void addUser(User user) throws OperationException {
        LOG.info("MySQLOperations: addUser()");
        final String query = "insert into wall_user (username, email, fname, lname, password) values (?, ?, ?, ?, ?)";
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBUtil.getDataSource().getConnection();
            ps = con.prepareStatement(query);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getEmail());
            ps.setString(3, user.getFirstName());
            ps.setString(4, user.getLastName());
            ps.setString(5, DigestUtil.sha256hex(user.getPassword()));
            int result = ps.executeUpdate();
            LOG.info("user stored with rows affected: " + result);
        }
        catch (SQLException ex) {
            LOG.info("error code: "+ex.getErrorCode());
            LOG.info("error msg: "+ex.getMessage());
            throw new OperationException(ex);
        }
        finally {
            DBUtil.close(null, ps, con);
        }
    }

    public void addWall(Wall wall) throws OperationException {
        final String query = "insert into wall_wall (wallname, title, userid) values (?, ?, ?)";
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBUtil.getDataSource().getConnection();
            ps = con.prepareStatement(query);
            //storing wall name in lower case:
            ps.setString(1, wall.getWallName().toLowerCase());
            ps.setString(2, wall.getWallTitle());
            ps.setInt(3, wall.getUserId());
            int result = ps.executeUpdate();
            LOG.info("wall stored with rows affected: " + result);
        }
        catch (SQLException ex) {
            throw new OperationException(ex);
        }
        finally {
            DBUtil.close(null, ps, con);
        }
    }

    public void addPost(Post post) throws OperationException {
        final String query = "insert into wall_post (userid, wallid, message) values (?, ?, ?)";
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBUtil.getDataSource().getConnection();
            ps = con.prepareStatement(query);
            ps.setLong(1, post.getUserId());
            ps.setLong(2, post.getWallId());
            ps.setString(3, post.getMessage());
            int result = ps.executeUpdate();
            LOG.info("message posted with affected rows: " + result);
        }
        catch (SQLException ex) {
            throw new OperationException(ex);
        }
        finally {
            DBUtil.close(null, ps, con);
        }
    }

    public List<Post> getUserPosts(String username) throws OperationException {
        final String query = "select u.id, u.username, w.id, w.wallname, p.message, p.ptime from wall_user u, wall_wall w, wall_post p where u.username = ?" + "and p.wallid = w.id  and u.id = p.userid order by p.ptime desc limit 100";

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getDataSource().getConnection();
            ps = con.prepareStatement(query);
            ps.setString(1, username);
            rs = ps.executeQuery();
            List<Post> posts = new ArrayList<Post>();
            while (rs.next()) {
                Integer userId = rs.getInt(1);
                String userName = rs.getString(2);
                Integer wallId = rs.getInt(3);
                String wallName = rs.getString(4);
                String message = rs.getString(5);
                Date d = rs.getDate(6);

                Post post = new Post();
                post.setUserId(userId);
                post.setUserName(userName);
                post.setWallId(wallId);
                post.setWallName(wallName);
                post.setMessage(message);
                post.setDate(d);

                posts.add(post);
            }
            return posts;
        }
        catch (SQLException ex) {
            throw new OperationException(ex);
        }
        finally {
            DBUtil.close(rs, ps, con);
        }
    }

    public Wall getWallPosts(String wallname) throws OperationException {
        LOG.info("\n\t MySQLOperations: getWallPosts(): " + wallname);
        final String query = "select w.id wallId, w.title wallTitle,w.userid wallBuiltByUserId, " +
                "u1.username wallBuiltByUserName,w.ptime builtOn, " +
                "u.id postedUserId, u.username postedUserName, " +
                "p.message postedMessage ,p.ptime postedOn " +
                "from wall_user u, wall_user u1, wall_wall w, " +
                "wall_post p where p.wallid = w.id and w.wallname=?  " +
                "and u.id = p.userid and w.userid =u1.id order by p.ptime desc limit 100";

        final List<Post> posts = new ArrayList<Post>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Wall w = null;
        try {
            con = DBUtil.getDataSource().getConnection();
            ps = con.prepareStatement(query);
            ps.setString(1, wallname);
            rs = ps.executeQuery();
            w = new Wall();
            w.setWallName(wallname);

            while (rs.next()) {
                final int wallId = rs.getInt("wallId");
                final String wallTitle = rs.getString("wallTitle");
                final int wallBuiltByUserId = rs.getInt("wallBuiltByUserId");
                final String wallBuiltByUserName = rs.getString("wallBuiltByUserName");
                final Date builtOn = rs.getDate("builtOn");

                final int postedUserId = rs.getInt("postedUserId");
                final String postedUserName = rs.getString("postedUserName");
                final String postedMessage = rs.getString("postedMessage");
                final Date postedOn = rs.getDate("postedOn");

                w.setWallId(wallId);
                w.setWallTitle(wallTitle);
                w.setUserId(wallBuiltByUserId);
                w.setUserName(wallBuiltByUserName);
                w.setDate(builtOn);
                LOG.info("\n\t wall *"+wallId+"* "+wallTitle+"* "+"* "+wallBuiltByUserId+"* "+wallBuiltByUserName+"* "+builtOn);
                
                Post post = new Post();
                post.setUserId(postedUserId);
                post.setUserName(postedUserName);
                post.setDate(postedOn);
                post.setMessage(postedMessage);
                LOG.info("\n\t post * "+postedUserId+"* "+postedUserName+"* "+postedOn+"* "+postedMessage);
                posts.add(post);
            }
            //Adding all posts in wall:
            w.setPost(posts);
            return w;
        }
        catch (SQLException ex) {
            throw new OperationException(ex);
        }
        finally {
            DBUtil.close(rs, ps, con);
        }
    }

    public List<Wall> getAllWall() throws OperationException {
        final String query = "select w.id,w.wallname,w.title,u.username,w.ptime from wall_wall w, wall_user u where u.id= w.userid";
        List<Wall> wList = new ArrayList<Wall>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = DBUtil.getDataSource().getConnection();
            ps = con.prepareStatement(query);
            rs = ps.executeQuery();
            while (rs.next()) {
                final Integer wallId = rs.getInt(1);
                final String wallName = rs.getString(2);
                final String wallTitle = rs.getString(3);
                final String userName = rs.getString(4);
                final Date date = rs.getDate(5);
                //build wall:
                Wall w = new Wall();
                w.setWallId(wallId);
                w.setWallName(wallName);
                w.setWallTitle(wallTitle);
                w.setDate(date);
                w.setUserName(userName);
                //add walls in wall list:
                wList.add(w);
            }
            return wList;
        }
        catch (SQLException ex) {
            throw new OperationException(ex);
        }
        finally {
            DBUtil.close(rs, ps, con);
        }
    }

    public LoginStatus login(final String usernameEmail, final String password) throws OperationException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = DBUtil.getDataSource().getConnection();
            ps = con.prepareStatement("select password from wall_user where username=? or email=?");
            ps.setString(1, usernameEmail);
            ps.setString(2, usernameEmail);
            rs = ps.executeQuery();
            if(rs.next()){
                final String dbPasswordHash = rs.getString("password");
                if(dbPasswordHash.equals(DigestUtil.sha256hex(password))){
                    return LoginStatus.SUCCESS;
                }
                else{
                    return LoginStatus.INVALID_PASSWORD;
                }
            }
            else{
                return LoginStatus.INVALID_USERNAME;
            }
        }
        catch(SQLException ex){
            throw new OperationException(ex);
        }
        finally{
            DBUtil.close(rs, ps, con);
        }
    }
}
